﻿using Dapper;
using DualReferralServer.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace DualReferralServer.Models
{
    public class DualReferralModel
    {
        static string config = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["SqlConnStr"].ConnectionString;
        IDbConnection connection = new SqlConnection(config);

        //一级科室（标准科室）查询
        public List<Department_standard> GetDepartment_Standards()
        {
            string sql = "SELECT LBCODE AS bzksdm, " +
                         "RTRIM(LBNAME) AS bzksmc " +
                         "FROM dbo.KSCODE(NOLOCK) " +
                         "GROUP BY LBCODE,LBNAME ";
            return connection.Query<Department_standard>(sql).ToList();
        }

        //二级科室查询
        public List<Department_hospital> GetDepartment_Hospitals(string temp)
        {
            string sql = "select LBCODE as bzksdm," +
                         "CODE as ksdm," +
                         "RTRIM(NAME) as ksmc," +
                         "RTRIM(LBNAME) as bzksmc, " +
                         "RTRIM(KSAddress) as jzdz " +
                         "from dbo.KSCODE (NOLOCK) " +
                         "where LBCODE like @LBCODE";
            return connection.Query<Department_hospital>(sql, new { LBCODE = '%' + temp }).ToList();
        }

        //医师查询
        public List<Doctor> GetDoctors(string kscode, string yscode)
        {
            string sql = "select rtrim(KSCODE) as ksdm, " +
                         "rtrim(KSNAME) as ksmc, " +
                         "CODE as ysgh, " +
                         "rtrim(NAME) as ysxm " +
                         "from YSCODE (nolock) " +
                         "where KSCODE like @KSCODE and CODE like @CODE ";
            return connection.Query<Doctor>(sql, new { KSCODE = '%' + kscode, CODE = '%' + yscode }).ToList();
        }

        //床位统计查询
        public List<Bed_statistics> GetBed_Statistics(string KSCode)
        {
            string sql = "SELECT b.M12 AS ksdm, " +
                         "b.M13 AS ksmc, " +
                         "a.cwzs AS cwzs, " +
                         "b.zycws AS zyzs " +
                         "FROM " +
                         "( " +
                         "SELECT  COUNT(*) AS cwzs " +
                         "FROM    KSCWSet " +
                         "WHERE   KSCODE LIKE @KSCODE " +
                         ") a, " +
                         "(SELECT M12, " +
                         "M13, " +
                         "COUNT(*) AS zycws " +
                         "FROM MBase " +
                         "WHERE M51 IS NOT NULL " +
                         "AND M53 IS NULL " +
                         "AND M12 LIKE @KSCODE " +
                         "GROUP BY M12 , " +
                         "M13) b ";
            return connection.Query<Bed_statistics>(sql, new { KSCODE = KSCode + '%' }).ToList();
        }

        //床位实时查询
        public List<Bed_realtime> GetBed_Realtimes(string KSCode)
        {
            string sql = "SELECT  RTRIM(a.CWNAME) AS cwdm ," +
                         " CASE WHEN CURRENTZYNUM IS NULL THEN '0'" +
                         "      ELSE '1'" +
                         " END AS sfzy ," +
                         " (SELECT    CONVERT(VARCHAR(10), M51, 120)" +
                         "   FROM MBASE" +
                         "   WHERE M01 = CURRENTZYNUM" +
                         " ) AS ryrq," +
                         " RTRIM(b.NAME) +' ' + a.CWNAME AS cwdz" +
                         " FROM KSCWSET a(NOLOCK)" +
                         "         LEFT JOIN dbo.KSCODE b ON a.KSCODE = b.CODE" +
                         " WHERE KSCODE = @KSCODE";
            return connection.Query<Bed_realtime>(sql, new { KSCODE = KSCode }).ToList();
        }
    }
}